﻿using API_Service.DBHelper;
using Dapper;
using System;
using System.Data;
using System.Linq;
using System.Reflection;

namespace API_Service.BizLogic.BasicData
{
    public class MOBaseService:BaseLogicService
    {
        private string _erpConnString = "";
        //private string connString = "";
        //private string _strDB = "yfdb";
        public MOBaseService()
        {
            _erpConnString = SqlCreator.ConnectionStrings["yfdb"];
            //connString = SqlCreator.ConnectionStrings[this._strDB];
        }

        public ExecutionResult<dynamic> SyncMOMasterDataFromERP()
        {
            ExecutionResult<dynamic> result = new ExecutionResult<dynamic>();
            try
            {
                string _strMaxCreateTime = "", _strMaxUpdateTime = "";
                //获取MES系统中当前物料最后更新时间。
                result = this.GetMESLastMOBaseERPDate();
                if (result.Status)
                {
                    var _tmpDate = result.Datas.AsList()[0];
                    _strMaxCreateTime = String.IsNullOrEmpty(_tmpDate.erp_create_time) ? "19990101" : _tmpDate.erp_create_time;
                    _strMaxUpdateTime = String.IsNullOrEmpty(_tmpDate.erp_update_time) ? "19990101" : _tmpDate.erp_update_time;
                    
                    //获取ERP更新数据
                    result = this.QueryERPNewMOInfo(_strMaxCreateTime, _strMaxUpdateTime);
                    if (result.Status)
                    {
                        //循环取数，并更新MES库数据
                        foreach (var mat in result.Datas.ToList())
                        {
                            bool _bExists = false;
                            //MOBaseInfo _tmpMat = (MOBaseInfo)mat;
                            //if (!string.IsNullOrEmpty(_strKPNPrefix) && !_strKPNPrefix.Contains(_tmpMat.Key_Part_no.Substring(1, 2)))
                            //{
                            //    continue;
                            //}
                            //check mo is exists
                            result = this.QueryMOBaseInfo(mat.Mo_No);
                            if (result.Status)
                            {
                                if (result.Datas.Length>0)
                                {
                                    var matId = result.Datas.ToList()[0].id;
                                    var matFlag = result.Datas.ToList()[0].is_valid;
                                    if (matFlag == "N")
                                    {
                                        continue;
                                    }
                                    //update
                                    string moStatus = result.Datas.ToList()[0].mo_status.ToString();
                                    if (moStatus.Equals("3"))
                                    {
                                        continue;
                                    }

                                    mat.Update_User = "SYSTEM_JOB";
                                    //mat.Update_Time = DateTime.Now;
                                    mat.Id = matId;
                                    result = this.UpdateMoBaseInfo(mat);
                                }
                                else
                                {
                                    //insert 
                                    mat.Create_User = "SYSTEM_JOB";
                                    //mat.Create_Time = DateTime.Now;
                                    result = this.InsertMoBaseInfo(mat);
                                }
                                //var matId = result.Datas.ToList()[0].id;
                                //var matFlag = result.Datas.ToList()[0].is_valid;
                                //if (matFlag == "N")
                                //{
                                //    continue;
                                //}
                                //_bExists = matId > 0 ? true : false;
                                //if (_bExists)
                                //{
                                //    //update
                                //    string moStatus = result.Datas.ToList()[0].mo_status.ToString();
                                //    if (moStatus.Equals("3"))
                                //    {
                                //        continue;                                        
                                //    }

                                //    mat.Update_User = "SYSTEM_JOB";
                                //    //mat.Update_Time = DateTime.Now;
                                //    mat.Id = matId;
                                //    result = this.UpdateMoBaseInfo(mat);
                                //}
                                //else
                                //{
                                //    //insert 
                                //    mat.Create_User = "SYSTEM_JOB";
                                //    //mat.Create_Time = DateTime.Now;
                                //    result = this.InsertMoBaseInfo(mat);
                                //}
                            }
                            if (!result.Status)
                            {
                                return result;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);

                result.Status = false;
                result.Message = "SyncMOMasterDataFromERP ex:"+ ex.Message;
            }

            return result;
        }


        public ExecutionResult<dynamic> GetMOBaseList()
        {
            var result = new ExecutionResult<dynamic>();

            return result;
        }
        private ExecutionResult<dynamic> GetMESLastMOBaseERPDate()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "SELECT max(erp_create_time) erp_create_time,max(erp_update_time) erp_update_time FROM dbo.r_mo_base_t WHERE is_valid = 'Y'";
                    conn.Open();
                    result.Datas = conn.Query(sql).ToArray();

                    result.Message = "获取MO数据更新时间成功";
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> QueryMOBaseInfo(string vMO = null, string vIsValid = null)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @"SELECT TOP 1000 [id]
      ,[mo_no]
      ,[mo_type]
      ,[target_qty]
      ,[mo_create_time]
      ,[mo_target_time]
      ,[cust_sku]
      ,[cust_code]
      ,[sku_no]
      ,[mo_kind]
      ,[bom_date]
      ,[bom_no]
      ,[bom_version]
      ,[character]
      ,[route_code]
      ,[default_line]
      ,[output_qty]
      ,[input_group]
      ,[output_group]
      ,[mo_status]
      ,[mo_start_time]
      ,[mo_close_time]
      ,[mo_online_time]
      ,[remarks]
      ,[erp_create_time]
      ,[erp_update_time]
      ,[is_valid]
      ,[create_time]
      ,[create_user]
      ,[update_time]
      ,[update_user]
                    FROM [scumes].[dbo].[r_mo_base_t] WHERE 1=1 ";
                    if (!String.IsNullOrEmpty(vMO))
                    {
                        sql = sql + " AND [mo_no] = @mo";
                    }
                    if (!String.IsNullOrEmpty(vIsValid))
                    {
                        sql = sql + " AND [is_valid] = @isValid";
                    }

                    result.Datas = conn.Query(sql, new { mo = vMO, isValid = vIsValid }).ToArray();
                    result.Status = true;
                    result.Message = "查询工单信息成功.";
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询工单基础信息异常。信息:" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> InsertMoBaseInfo(dynamic _tmpMat)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                //var _tmpMat = (MOBaseInfo)vModel;
                try
                {
                    string _insertSql = @"   insert into [scumes].[dbo].[r_mo_base_t]
                                  values(@mo_no,
                                @mo_type,
                                @target_qty,
                                @mo_create_date,
                                @mo_target_date,
                                @cust_sku,
                                @cust_code,
                                @sku_no,
                                @mo_kind,
                                @bom_date,
                                @bom_no,
                                @bom_version,
                                @character,
                                @route_code,
                                @default_line,
                                @output_qty,
                                @input_group,
                                @output_group,
                                @mo_status,
                                @mo_start_time,
                                @mo_close_time,
                                @mo_online_time,
                                @remarks,
                                @erp_create_time,
                                @erp_update_time,
                                @is_valid,
                                @create_time,
                                @create_user,
                                @update_time,
                                @update_user)  ";
                    var dbParams = new DynamicParameters();

                    //dbParams.Add("@id", _tmpMat.Id, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@mo_no", _tmpMat.mo_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_type", _tmpMat.mo_type, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@target_qty", _tmpMat.target_qty, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_create_date", _tmpMat.mo_create_date, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_target_date", _tmpMat.mo_target_date, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@cust_sku", _tmpMat.cust_sku, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@cust_code", _tmpMat.cust_code, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@sku_no", _tmpMat.sku_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_kind", _tmpMat.mo_kind, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@bom_date", _tmpMat.bom_date, DbType.Date, ParameterDirection.Input);
                    dbParams.Add("@bom_no", "", DbType.String, ParameterDirection.Input);//
                    dbParams.Add("@bom_version", _tmpMat.bom_version, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@character", "", DbType.String, ParameterDirection.Input);//
                    dbParams.Add("@route_code", "", DbType.String, ParameterDirection.Input);//
                    dbParams.Add("@default_line", "", DbType.String, ParameterDirection.Input);//
                    dbParams.Add("@output_qty", "", DbType.Int32, ParameterDirection.Input);//
                    dbParams.Add("@input_group", "", DbType.String, ParameterDirection.Input);//
                    dbParams.Add("@output_group", "", DbType.String, ParameterDirection.Input);//
                    dbParams.Add("@mo_status", _tmpMat.mo_status, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_start_time", "", DbType.DateTime, ParameterDirection.Input);//
                    dbParams.Add("@mo_close_time", "", DbType.DateTime, ParameterDirection.Input);//
                    dbParams.Add("@mo_online_time", "", DbType.DateTime, ParameterDirection.Input);//
                    dbParams.Add("@remarks", _tmpMat.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_create_time", _tmpMat.erp_create_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_update_time", _tmpMat.erp_update_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", _tmpMat.is_valid, DbType.String, ParameterDirection.Input);//
                    dbParams.Add("@create_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@create_user", _tmpMat.Create_User, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@update_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@update_user", _tmpMat.Create_User, DbType.String, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "添加MO信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "添加MO信息失败";
                    }
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "添加MO信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> UpdateMoBaseInfo(dynamic _tmpMat)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                //var _tmpMat = (MOBaseInfo)vModel;
                try
                {
                    string _insertSql = @"update [scumes].[dbo].[r_mo_base_t]
                              set  [mo_no]=@mo_no      
                                  ,[mo_type] =@mo_type     
                                  ,[target_qty]=@target_qty      
                                  ,[mo_create_time]=@mo_create_date     
                                  ,[mo_target_time]=@mo_target_date      
                                  ,[cust_sku]=@cust_sku
                                  ,[cust_code]=@cust_code      
	                              ,[sku_no]=@sku_no      
	                              ,[mo_kind]=@mo_kind      
	                              ,[bom_date]=@bom_date      
	                              --,[bom_no]=@bom_no      
	                              ,[bom_version]=@bom_version      
	                              --,[character]=@character
                                  --,[route_code]=@route_code      
	                              --,[default_line]=@default_line      
	                              --,[output_qty]=@output_qty      
	                              --,[input_group]=@input_group      
	                              --,[output_group]=@output_group      
	                              ,[mo_status]=@mo_status
                                  --,[mo_start_time]=@mo_start_time      
	                              --,[mo_close_time]=@mo_close_time      
	                              --,[mo_online_time]=@mo_online_time      
	                              ,[remarks]=@remarks      
	                              ,[erp_create_time]=@erp_create_time      
	                              ,[erp_update_time]=@erp_update_time
                                  ,[is_valid]=@is_valid      
	                              --,[create_time]=@create_time      
	                              --,[create_user]=@create_user      
	                              ,[update_time]=@update_time      
	                              ,[update_user]=@update_user
                              where [id]=@id ";
                    var dbParams = new DynamicParameters();

                    dbParams.Add("@id", _tmpMat.Id, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@mo_no", _tmpMat.mo_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_type", _tmpMat.mo_type, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@target_qty", _tmpMat.target_qty, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_create_date", _tmpMat.mo_create_date, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_target_date", _tmpMat.mo_target_date, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@cust_sku", _tmpMat.cust_sku, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@cust_code", _tmpMat.cust_code, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@sku_no", _tmpMat.sku_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_kind", _tmpMat.mo_kind, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@bom_date", _tmpMat.bom_date, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@bom_no", _tmpMat.Remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@bom_version", _tmpMat.bom_version, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@character", _tmpMat.Type, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@route_code", _tmpMat.Description, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@default_line", _tmpMat.Spec, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@output_qty", _tmpMat.Unit, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@input_group", _tmpMat.Remarks, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@output_group", _tmpMat.Spec, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@mo_status", _tmpMat.mo_status, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@mo_start_time", _tmpMat.Remarks, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@mo_close_time", _tmpMat.Id, DbType.Int32, ParameterDirection.Input);
                    //dbParams.Add("@mo_online_time", _tmpMat.Type, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@remarks", _tmpMat.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_create_time", _tmpMat.erp_create_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_update_time", _tmpMat.erp_update_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", _tmpMat.Is_Valid, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@update_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@update_user", _tmpMat.Update_User, DbType.String, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新MO信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新MO信息失败";
                    }
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新MO信息异常:"+ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> DeleteMoBaseInfo(int vId)
        {
            var result = new ExecutionResult<dynamic>();

            string _insertSql = @"DELETE FROM [dbo].[r_mo_base_t] WHERE[id] = @id";
            var dbParams = new DynamicParameters();
            dbParams.Add("@id", vId, DbType.Int32, ParameterDirection.Input);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "删除工单信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "删除工单信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "删除工单信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> QueryERPNewMOInfo(string vCreateTime, string vUpdateTime)
        {
            var result = new ExecutionResult<dynamic>();
            string sql = @"  SELECT TOP 300 TA002 as mo_no,TA001 as mo_type,TA015 as target_qty, TA009 as mo_create_date,TA010 as mo_target_date, 
TA034 as cust_sku, TA035 as cust_code, TA006 as sku_no, TAC01 as mo_kind, TA004 as bom_date, TA005 as bom_version,
TA011 as mo_status,TA029 as remarks, CREATE_DATE as erp_create_time,MODI_DATE as erp_update_time 
FROM dbo.MOCTA WHERE (CREATE_DATE >@CREATE_DATE or MODI_DATE >@UPDATE_DATE)  ";
            var dbParams = new DynamicParameters();
            dbParams.Add("@CREATE_DATE", vCreateTime, DbType.String, ParameterDirection.Input);
            dbParams.Add("@UPDATE_DATE", vUpdateTime, DbType.String, ParameterDirection.Input);
            //链接ERP DB，查询物料信息
            using (IDbConnection conn = SqlCreator.Create(DataBaseType.SqlServer,this._erpConnString))
            {
                try
                {                    
                    result.Datas = conn.Query<dynamic>(sql, dbParams).ToArray();
                    result.Status = true;
                    result.Message = "Get ERP MO data success.";
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + result.Message);
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
    }

    public class MOBaseInfo
    {
        public int Id { get; set; }
        public string Mo_No { get; set; }
        public string Mo_Type { get; set; }
        public string Target_Qty { get; set; }
        public string Mo_Create_Date { get; set; }
        public string Mo_Target_Date { get; set; }
        public string Cust_Sku { get; set; }
        public string Cust_Code { get; set; }
        public string Sku_No { get; set; }
        public string Mo_Kind { get; set; }
        public string Bom_Date { get; set; }
        public string Bom_No { get; set; }
        public string Bom_Version { get; set; }
        public string Character { get; set; }
        public string Route_Code { get; set; }
        public string Default_Line { get; set; }
        public string Output_Qty { get; set; }
        public string Input_Group { get; set; }
        public string Output_Group { get; set; }
        public string Mo_Status { get; set; }
        public string Mo_Start_Time { get; set; }
        public string Mo_Close_Time { get; set; }
        public string Mo_Online_Time { get; set; }
        public string Remarks { get; set; }
        public string Erp_Create_Time { get; set; }
        public string Erp_Update_Time { get; set; }
        private string _isValid = "Y";
        public string Is_Valid
        {
            get { return this._isValid; }
            set { this._isValid = String.IsNullOrEmpty(value) ? this._isValid : value; }
        }
        private string _sCreateUser = "SYSTEM_JOB";
        public string Create_User
        {
            get { return this._sCreateUser; }
            set { this._sCreateUser = String.IsNullOrEmpty(value) ? this._sCreateUser : value; }
        }
        public DateTime Create_Time { get; set; }

        private string _sUpdateUser = "SYSTEM_JOB";
        public string Update_User
        {
            get { return this._sUpdateUser; }
            set { this._sUpdateUser = String.IsNullOrEmpty(value) ? this._sUpdateUser : value; }
        }
        public DateTime Update_Time { get; set; }
    }
}